SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[NAVCalcInputs] (@FundId VARCHAR(50),@StartDate SMALLDATETIME, @EndDate SMALLDATETIME)  AS
BEGIN

		DECLARE @ExportId INT = (SELECT MAX(e.ExportId) FROM [FSFundModel].Reporting.Export e WHERE e.FundId = @FundId)
		DECLARE @MostRecentFinalDate SMALLDATETIME = (
		SELECT [rdn].[NAVDate] FROM  [FSFundModel].[Reporting].[DailyNAV] rdn 
		WHERE  [rdn].[ExportId] = @ExportId
		AND [rdn].[DailyNAVStatusType] = 'Final')

;WITH pvt AS (
SELECT [Date], [FundId], [Interest], [Amortization], [Fee] FROM 
(SELECT [Date], [FundId], [SumReportDateAmt], [FSType] FROM [SSB].[rollupByFund_FSType_Date]
		WHERE [FundId] = @FundId AND [Date] BETWEEN @StartDate AND @EndDate
) AS t1
PIVOT(MAX([t1].[SumReportDateAmt]) FOR  [FSType] IN ([Interest], [Amortization], [Fee])) AS t2
)
, NAVTbl AS (
SELECT * FROM [FSFundModel].[dbo].[DailyNAV] dn WHERE [dn].[FundId] = @FundId
AND [dn].[NAVDate] < @MostRecentFinalDate

UNION ALL SELECT 	
 [FundId]
, [NAVDate], [DailyNAVStatusType], [DailyEqValChg], [MktValPerShareChg], [TRSUnrealApprec], [TRSRealInc], [TRSValChg], [TRSValShareChg], [UnrealApprec], [UnrealDeprec], [RealGain]
, [PrevPdCapGainsInctFee], [UnrealApprecInctFee], [UnrealApprecInctFeeChg], [UnrealApprecInctFeeShareChg], [EqValShareChg], [SharesOut], [SharesRaised], [GrossRaise], [CommOandO]
, [AccretDilut], [DailyMktValPL], [FXAdjMktValPL], [FXChgPL], [FXChgShare], [NavAdjust], [EstNAVShare], [NAVShare], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [DailyChg]
, [EstNAVShareChgPct], [RealGainLossEdit], [RealGainLossToday], [RealGainLossTodayActive], [RealGainLossTodayExited], [DailyValChgActive], [DailyValChgExited], [DailyValChgUnfunded]
, [UnsettForAssetsShare], [CommOandOEdit], [TRSUnrealApprecEdit], [TRSRealIncEdit], [CommOandOLocked], [TRSUnrealApprecLocked], [TRSRealIncLocked], [RealGainLocked], [SharesOutLocked]
, [SharesOutEdit] FROM [FSFundModel].[Reporting].[DailyNAV] dn
WHERE [dn].[ExportId] =@ExportId

 ) 

SELECT  dn.NAVDate ,
        dn.DailyEqValChg AS DVC,
        dn.RealGainLossToday AS [Realized Gains],
        dn.UnrealApprec ,
        dn.UnrealDeprec ,
        ISNULL(NULLIF(dn.NAVShare, 0), dn.EstNAVShare) FinalNAV ,
        dn.NAVShare UserNav ,
        dn.EstNAVShare ArtyNAVCalc ,
				COALESCE(CAST([pvt].Interest AS VARCHAR(20)), '#N/A') AS [Interest],
				COALESCE(CAST( [pvt].Amortization AS VARCHAR(20)), '#N/A') AS [Amortization],
			  COALESCE(CAST([pvt].Fee AS VARCHAR(20)), '#N/A') AS [Fee]
        , dn.DailyNAVStatusType
FROM    [NAVTbl] dn
LEFT JOIN [pvt] ON [pvt].[Date] = [dn].[NAVDate] 
WHERE   dn.FundId = @FundId
        AND dn.NAVDate BETWEEN @StartDate AND @EndDate
				--AND [dn].[ExportId] = (SELECT MAX(e.ExportId) FROM [FSFundModel].Reporting.Export e WHERE e.FundId = @FundId)
				AND [dn].[FundId] = @FundId
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live', 'SCHEMA', N'dbo', 'PROCEDURE', N'NAVCalcInputs', NULL, NULL
GO
